Returns 1 if exp returns an error other than @NA. Returns 0 otherwise.
@ISERR(@ERR) -> 1 (true)
@ISERR(@VALUE("ABCD") -> 1 (true) : This formula tried to convert "ABCD" to a value using @VALUE(). This returns !ERR, which causes @ISERR() to return 1.
Returns the day of the month for a date serial number.
A3: 18-Jul-92
=DAY(A3) -> 18
=DAY(NOW) : This formula will return the current day of the month.
LENGTH
Returns the number of characters in the string.
LENGTH (``10% discount on COD'') = 19
A1= ``A short string '', B1=``sentence''
LENGTH(A1) = 14
LENGTH(A1&B1) = 22
@COUNT
Counts the number of numeric or string entries. Cells or items containing strings are counted. Cells or items containing blanks are not counted.
A B
1 Duck 10
2 Soup 20
3 1.86 20
4 $300 10
5 '====== 30
6 50
7 40 70
@COUNT(A1:A7) = 6: There are only six items in column A because cell A6 is blank.
@COUNT(A1:A7,B1:B7) = 13: This counts the 6 items in column A and adds the 7 items in column B.
@COUNT("Good","Bad","Indifferent") = 3
Note: This function differs from COUNT(num|range[,...]). @COUNT() counts string entries. COUNT() ignores string entries and returns the count of cells that contain numeric values.
wReturns the Absolute Value of the number.
ABS(-45.3) = 45.3
ABS(COS(PI)) = 1
ABS(34) = 34
ABS(``Absolutely!'') = 0
DCOUNT
Counts all the numeric items matching criteria in the column offset from the upper left corner of the database range.
This is a sample database:
A B C D
1 NAME GRADE GPA GRADE
2 Joe B 3.2 B
3 Sue A 4.7
4 Robert C 2.7
5 Steve A 4.5
6 Ann B 4.2
The database range is A1:C6. The criteria range is D1:D2.
To count the number of students who earned B's use this formula:
=DCOUNT(A1:C6,1,D1:D2) -> 2
@DIDCHANGE
.Returns a 1 if the sheet has changed since the last time it was saved, and a 0 if nothing has changed. Since the act of adding this function changes the sheet, it will only show as true except when the sheet has been recalculated right after it has been saved. This can be useful in MScript scripts.
@NEXTMONTH
Returns a date serial number one calendar month after date. If the day of the month is greater than 28, it is set to 28.
A3: 27-Jul-92
@NEXTMONTH(A3) -> 8274 : This is the date-number for
27-Aug-92.
@NEXTMONTH(11381) = 11409 : This is the date-number for 28-Mar-2001.
USERNAME
Returns the user's login name.
PATHNAME
Returns the current path name.
INDIRECT
Evaluates string as a cell address and returns that address.
A3: Cheers
B3: A3
INDIRECT(B3) -> Cheers
A1: 10
A2: 20
A3: 30
B1: A1:A3
@SUM(INDIRECT (B1)) -> 60
@BEEPIF
Beeps if the condition is true and returns 1, 0 if false.
C7: 100
@BEEPIF(C7<200) : This formula will beep one time.
@BEEPIF(@rand>0.5) : This formula will generate a random number between 0 and 1. If the number is > 0.5, the it beeps and returns 1. Otherwise it returns 0.
@DAYMONFORMAT
cconstant is used to specify date day-month format.
@FORMAT(9876.54321, @DAYMONTHFORMAT) -> 15-Jan
Returns the number of rows in a range.
=ROWS(A1:H20) -> 20
If RANGE1 is the name assigned to the range B1:CC200, =ROWS(RANGE1) -> 200
@SETALTCOLOR
Sets the alternate color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
@SETALTCOLOR(A1,@IF(A1 < -100,1,0),0,0) : This formula sets the alternate color to red if the number is less than -100, otherwise sets the alternate color to black.
CONVDATE
Converts a number in the format YYMMDD to a date serial number. Dates after the year 2000 can be specified by placing a 1 before the year.
=CONVDATE(930210) -> 8441 : This is the date-number of 10-Feb-93
=CONVDATE(1100410) -> 14709 : This returns the date number of 10-Apr-2010.
PERCENTFORMAT
aThis constant is used to specify the percent format.
=FORMAT(0.09389, PERCENTFORMAT, 2) -> 9.39%
@COLS
Returns the number of columns in a range.
@COLS(A1:H20) -> 8
If RANGE1 is the name assigned to the range B1:CC200, @COLS(RANGE1) -> 80
DKGRAYCOLOR
[A constant referring to Dark GRAY for use in functions that change color (e.g. SETCOLOR())
DINTL1FORMAT
yThis constant is used to specify the numeric date month-day-year format.
=FORMAT(9876.54321, DINTL1FORMAT) -> 01/15/97
@CURRENCYFORMAT
bThis constant is used to specify the currency format.
@FORMAT(555.9, @CURRENCYFORMAT) -> $555.90
FIXEDFORMAT
bThis constant is used to specify the fixed decimal format.
=FORMAT(42.692, FIXEDFORMAT) -> 42.69
@RATE
Returns the interest rate for an investment of pv to achieve a value of fv in per periods.
How much interest would an a bank account need to earn for an investment of $67,000 to grow to $100,000 in 5 years or less?
@RATE(100000,67000,5) -> 8.34% or more.
Returns the fractional component of num.
=FRAC(NOW) : This formula returns only the time component of the current date.
=FRAC(3.14159) -> 0.14159
(Returns the current date serial number.
JReturns the area of a circle with a radius of 1, or approximately 3.1415.
@HOURMINFORMAT
lThis constant is used to specify date hours-minutes format.
@FORMAT(9876.54321, @HOURMINFORMAT) -> 1:02 PM
@CODE
Returns the ASCII code for the first character in string.
@CODE("A") -> 65
@CODE("{") -> 123
@CODE("Hello") -> 72 : This formula is returning the ASCII code for "H"
Returns the logarithm, base 10, of num.
@LOG(45) = 1.6532
+10^(@LOG(292)) -> 292: This formula takes the base 10 log of 292, then raises 10 to that power, resulting in the number it began with.
Takes the average of numeric records that match criteria.
This is a sample database:
A B C D
1 NAME GRADE GPA GRADE
2 Joe B 3.2 B
3 Sue A 4.7
4 Robert C 2.7
5 Steve A 4.5
6 Ann B 4.2
The database range is A1:C6. The criteria range is D1:D2.
To calculate the average GPA of everyone who earned a B use this formula:
=DAVE(A1:C6,2,D1:D2) -> 3.7
@DATE
MReturns a date serial number for the year, month and day. A date serial number is the number of days since January 1, 1970. Dates after the year 2000 can be specified by placing a 1 before the year.
@DATE(92,8,24) -> 8271 : This is the date number for
24-Aug-92.
@DATE(79,10,31) -> 3590 : This is the date-number for
31-Oct-79.
Returns the Arccosine of num (in radians). ACOS(COS(num)) = num.
What is the angle with the cosine = 0.7071?
=ACOS(0.7071) -> 0.7854 (radians) : This is equivalent to an angle of 45 degrees.
=ACOS(0.7071)*180/pi -> 45 : This gives the same answer as above, but in degrees.
TINTL1FORMAT
{This constant is used to specify twenty four hour time format with seconds.
=FORMAT(9876.54321, TINTL1FORMAT) -> 13:02:13
CReturns the Cosine of num.
@COS(@PI) -> -1
@COS(@LN(3)) -> 0.4548
Divides the sum of the numbers by the count of numeric and string entries, using the same rules for counting as @COUNT().
@AVE(4,5,25,3) = 9.25
Given the following data:
A1: 184
A2: 592
A3: 97
@AVE(A1:A3) = 291
Note: This function differs from AVE(num|range[,...]) or AVERAGE(num|range[,...]). @AVE() and @AVG() treat string entries as having a zero value, but AVE() and AVERAGE() ignore string entries.
@HISTOGRAM
uGroups the values in range into #-of-divisions divisions, counts the number of items in each division. The formula should appear in #-of-divisions cells, starting at anchor. It returns the number of items in the division that is offset from the anchor point.
A B
1 Name GPA
2 Joe 2.6
3 Sue 3.7
4 Robert 3.2
5 Steve 3.0
6 Ann 3.8
7 John 3.5
8 Mary 2.9
9 Chris 3.5
To divide the students into 5 divisions by GPA and determine how many fall into each division:
A10: =HISTOGRAM($B$2:$B$9,5,$A$10) -> 1
B10: =SAME($A$10) -> 2
C10: =SAME($A$10) -> 1
D10: =SAME($A$10) -> 2
E10: =SAME($A$10) -> 2
@TIMEVALUE
YConverts a string to a date serial number.
A1: '10
A2: '58
A3: '29
@TIMEVALUE(A1&":"&A2&":"&A3) -> 0.45728 : This formula takes the string contents of cells A1, A2 & A3 and concatenates them into the string "10:58:29", then converts that string to a time value.
A3: 12:34 am, @TIMEVALUE(A3) -> 0.023611 : This is the date-number for 12:34 am.
PROPER
Converts str to "proper" capitalization. It will turn the first letter of each word to upper case; otherwise, it converts the letter to lower case.
=PROPER("MS. JANET KRAFT") -> Ms. Janet Kraft
=PROPER("all entries posted before 7/2") -> All Entries Posted Before 7/2
A bit of history: Upper and lower case does not refer to the size of the letters, but to where they were stored in type cabinets. The capital letters were stored in the "Upper Case," while the small letters were stored in the "Lower Case."
@ISSTRING
Returns 1 is exp results in a string. Returns 0 otherwise.
@ISSTRING(@VALUE("340")) -> 0
@ISSTRING(@LEFT("Goodbye!",4)) -> 1
@ISSTRING("Some text") -> 1
BEEPIF
Beeps and returns 1 if the condition is true, returns 0 if false.
C7: 100
=BEEPIF(C7<200) : This formula will beep one time.
=BEEPIF(rand>0.5) : This formula will generate a random number between 0 and 1. If the number is > 0.5, the it beeps and returns 1. Otherwise it returns 0.
@SETCOLOR
Sets the color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
@SETCOLOR(A1,@IF(A1 > 100,1,0),@IF(A1 > 100,0,1),0) : This formula sets the color to red if the number is greater than 100, otherwise sets the color to green.
REPLACE
Replaces num characters in str starting offset characters from the first character with the string new-str.
=REPLACE("The cost is $450, COD",13,3,"2,500") -> The cost is $2,500, COD
=REPLACE("Robert J. Stevenson",7,1,"L") -> Robert L. Stevenson
@ATAN2
!Returns the Arctangent of x/y. This gives the angle of a line from the origin to x,y.
@ATAN2(3,1) -> 0.3217 (radians): This is equivalent to an angle of 18.4 degrees.
This function differs from ATAN2(x,y). @ATAN2() requires the Y parameter first. ATAN2() requires the X parameter first.
@TODAY
YReturns the current date serial number without the current time (only the current date.)
@DKGRAYCOLOR
\A constant referring to Dark GRAY for use in functions that change color (e.g. @SETCOLOR())
@ISEMPTY
@Returns 1 if the cell at adr contains no value, else returns 0.
@WHITECOLOR
XA constant referring to WHITE for use in functions that change color (e.g. @SETCOLOR())
@REPEAT
Returns string repeated times times.
@REPEAT("X",10) -> XXXXXXXXXX
@REPEAT("over and ",4) -> over and over and over and over and
Returns the square root of the number.
=SQRT(34) -> 5.8310
=SQRT(LOG(200)) -> 1.5169
=SQRT(VAR(200,500,100)) -> 170 : This formula computes the standard deviation of the values given in the variance function.
@ROWS
Returns the number of rows in a range.
@ROWS(A1:H20) -> 20
If RANGE1 is the name assigned to the range B1:CC200, @ROWS(RANGE1) -> 200
@ROOT
/Returns the nth root of num.
@ROOT(81,4) -> 3
TRUE()
Returns a logical true value.
CALCRATE
XCalculates the interest rate such that present-value = @PV(payment,interest-rate,term).
ISREF
Returns 1 if exp is a cell or range reference. Returns 0 otherwise.
=ISREF(A1) -> 1 (true)
If RANGE1 has been assigned A1:C10, =ISREF(RANGE1) -> 1
EXACT
0Returns 1 if the strings match exactly. Returns 0 otherwise.
=EXACT("Daytime","Daytyme") -> 0 (false)
A1="hello" and B1="hello": =EXACT(A1,B1) -> 1 (true)
Note: When you use = to match strings, the comparison ignores case. For example, "Upper" = "upper" is true, but EXACT("Upper", "upper") is false.
@TRIM
Removes leading and trailing spaces from string. It also converts multiple consecutive spaces in string to single spaces.
A3: Account # 3040302 40303
@TRIM(A3) -> Account # 3040302 40303
@TRIM(" Anything at all.") -> Anything at all.
@NEXTYEAR
Returns the next year of the date. If the date is 2/29 of a leap year, the date returned is 2/28 of the following year.
@NEXTYEAR(@NOW) : This formula returns a date-number one year from the current date.
@NEXTYEAR(@DATE(93,6,20)) -> 8936 : This is the date-number for 20-Jun-94.
@SIGN
Returns 0 if num is zero, -1 if num is less than 0, and 1 if num is greater than 0.
B7: @RAND
@SIGN(B7-0.5) : This formula returns the sign of a randomly generated number between 0.5 and -0.5.
@SIGN(-405) -> -1
AVERAGE
~Divides the sum of the numbers by the count of numeric entries. Uses the same rules for counting as COUNT().
=AVERAGE(4,5,25,3) -> 9.25
Given the following data:
A1: 184
A2: 592
A3: 97
=AVE(A1:A3) -> 291
Note: This function is different than @AVE(num|range[,...]) or @AVG(num|range[,...]). AVE() and AVERAGE() ignore string values. @AVE() and @AVG() treat string values as zero.
@DINTL2FORMAT
pThis constant is used to specify the numeric date month-day format.
@FORMAT(9876.54321, @DINT2FORMAT) -> 01/15
@DVAR
Returns the variance of the records that match the criteria. Strings have a value of zero.
This is a sample database:
A B C D
1 STATE REGION AVG RF REGION
2 AL South 3.7 West
3 CA West 2.1 South
4 NJ East 5.2
5 FL South 4.9
6 LA South 6.8
7 TX West 5.0
8 MA North 7.6
9 AZ West 2.2
10 NV West 2.4
The database range is A1:C10. The criteria range is D1:D3.
To find the variance of rainfall in the Southern and Western regions use this formula:
@DVAR(A1:C10,2,D1:D3) -> 2.7192
INDEX
Returns the cell that is row rows and col columns offset from the upper left corner of range.
A B C
1 10 20 30
2 3942 4932 5929
3 4920 5929 9294
=INDEX(A1:C3,2,2) -> 9294
A10: 1
A11: 0
=INDEX(A1:C3,A10,A11) -> 3942
Note: This function is different from @INDEX(range,col,row). INDEX() specifies the offset in row then column order. @INDEX() specifies the offset in column then row order.
Returns the Sum of the Years Digits depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 4?
year 1: =SYD(12000,2000,5,1) -> $3,333
year 2: =SYD(12000,2000,5,2) -> $2,667
year 4: =SYD(12000,2000,5,4) -> $1,333
@ISNUMBER
0Returns 1 if exp returns a numeric result. Returns 0 otherwise.
@ISNUMBER(@STRING(300,0)) -> 0 : This formula converts the number "300" to a string using @STRING(). It then uses @ISNUMBER() to test if that result is a value and returns 0 (false).
@ISNUMBER(34) -> 1
A3: +394 - 100
@ISNUMBER(A3) -> 1
FReturns a random number between 0 and 1, including 0 and excluding 1.
TODAY
@CHAR
5Returns the ASCII character for num.
@CHAR(68) -> D
MReturns the straight-line depreciation of an asset that cost cst, has a salvage value of slvg, and a life of life.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off each year?
=SLN(12000,2000,5) -> $2,000
3Computes the Net Present Value of an investment with a given stream of income at a given interest rate. Each payment can be a value or a range. If it is a range, each of the numbers in the range is included in the calculation.
An apartment building is being offered for sale for $400,000. Expected rental incomes for years 1-5 are $79,000, $85,000, $85,000, $103,000 and $120,000 and the interest rate is expected to stay close to 7%. Is this a worthwhile investment?
A
1 -400000
2 79000
3 85000
4 85000
5 103000
6 120000
To figure out the net present value of the cash in-flows:
=NPV(7%,A2:A6) -> $381,596
To figure out the value of the project:
Project Value = Initial Cost + NPV
+A1+NPV(7%,A2:A6) -> -$18,404
No, this project is not worthwhile.
Calculates the payment required for a loan payoff.
What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%?
=PMT(9%/12,5*12,-40000) -> $830.33 per month.
UPPER
Converts all the characters in str to upper case.
=UPPER("Uppers only") -> UPPERS ONLY
=UPPER(MID("robert c. smith",8,2)) -> C. : This formula uses MID() to return the 8th and 9th characters from the string "Robert C. Smith". The UPPER() function then capitalizes this result.
@ATAN
pReturns the Arctangent of num.
@ATAN(5.6713) = 1.3962 (radians): This is equivalent to an angle of 80 degrees.
nIf num is zero or not a number, 1 is the result; otherwise, 0 is the result.
=NOT(34) -> 0
=NOT("Text") -> 1
Returns a string containing the first num characters from the left of str.
=LEFT("The first twelve characters",12) -> The first tw
=LEFT("ABCDEFG",4) -> ABCD
@TINTL2FORMAT
|This constant is used to specify twenty four hour time format without seconds.
@FORMAT(9876.54321, @TINTL2FORMAT) -> 13:02
jReturns the variance of the numeric records that match the criteria.
This is a sample database:
A B C D
1 STATE REGION AVG RF REGION
2 AL South 3.7 West
3 CA West 2.1 South
4 NJ East 5.2
5 FL South 4.9
6 LA South 6.8
7 TX West 5.0
8 MA North 7.6
9 AZ West 2.2
10 NV West 2.4
The database range is A1:C10. The criteria range is D1:D2.
To find the variance of rainfall in the Southern and Western regions use this formula:
=DVAR(A1:C10,2,D1:D3) -> 2.7192
STDEV
Results in the standard deviation of the ranges or list. Blank cells and strings are not counted.
A B
1 NAME SCORE
2 Anna 65.8
3 Bill 95.4
4 Donna 30.2
5 Mark 54.9
6 Maria 35.1
7 Susan 75.9
8 John 83.2
9 Rob 33.1
10 Ethan 81.8
=STDEV(B2:B10) -> 23.1
Note: This functions are different than @STD(num|range[,...]). STDDEV() and STDEV() ignore string values. @STD() treats string values as zero.
@MONYEARFORMAT
hThis constant is used to specify date month-year format.
@FORMAT(9876.54321, @MONYEARFORMAT) -> Jan-97
&Calculates the internal rate of return of an investment.
A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return?
A
1 -180000
2 25000
3 35000
4 39000
5 40000
6 55000
The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value:
=IRR(B1,A1:A6) -> 2.3%
Returns the Arcsine of num (in radians). ASIN(SIN(num)) = num.
What is the angle whose sine = 0.3090?
=ASIN(0.3090) -> 0.3141 (radians) : This is equivalent to an angle of 18 degrees.
=ASIN(0.3090)*180/pi -> 18 : This gives the same answer as above, but in degrees.
Returns the smallest numeric item in the records that match the criteria.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C6. The criteria range is D1:D2.
To find the smallest sales figure in the Eastern region use this formula:
=DMIN(A1:C6,1,D1:D2) = 2800
cReturns the location of sub-str in str. It starts searching for sub-str offset characters from the left of str. Returns an error if the string is not found.
=FIND("hampton","Northampton",1) -> 4
=FIND("hampton","Northampton",6) -> !ERR (because it starts searching at the 6th character)
A1: "The third word in this sentence."
=FIND("word",A1,1) -> 10
Returns the remainder of num divided by div.
=MOD(12,5) -> 2
=NOW-MOD(NOW,7)+5 : This function returns the date-number for Monday of the current week..
>Computes the Sine of num (expressed in radians).
@SIN(60*PI/180) -> 0.8660 : This formula gives the sine of 60 degrees.
A mountain road goes up at an incline of 25 degrees. If the road is straight and is 20 miles long, what vertical distance will a car travel to climb it?
+20 * @SIN(25*(@pi/180)) -> 8.45 miles
The result is the statistical variance of the numbers. Strings are counted as 0, and blank cells are not counted.
A B
1 NAME SCORE
2 Anna 65.8
3 Bill 95.4
4 Donna 30.2
5 Mark 54.9
6 Marie 35.1
7 Susan 75.9
8 John 83.2
9 Rob 33.1
10 Ethan 81.8
@VAR(B2:B10) = 531.6010
Note: This function differs from VAR(num|range[,...]). @VAR() counts string values as zero, but VAR() ignores string values.
CELLPOINTER
Returns the value for the attribute of the currently active cell after a recalculation of the sheet. This is valuable for macros. See CELL() or @CELL() for information about attributes.
@CURRENTVALUE
The current value of the cell (before the recalc). This is useful for comparing old cell values with other values, such as taking the larger of the current value or the value of another cell.
Results in the largest number in the range or list.
=MAX(4.5,3.2,2.5,2.5,6.2) -> 6.2
=MAX(4,4,4) -> 4
A B C
1 300 400
2 0 400
3 200 100
4 300 5000
5 700
6 600
7 300
8 500
=MAX(A1:A8,C1:C8) -> 5000
Note: This function is different than @MAX(num|range[,...]). MAX() ignores string values. @MAX() treats string values as zero.
@FRAC
Returns the fractional component of num.
@FRAC(@NOW) : This formula returns only the time component of the current date.
@FRAC(3.14159) -> 0.14159
Returns a string that is len characters taken from str starting start characters from the left of str.
=MID(``Have a great day!'',8,5) -> great
A1 = ``Robert Quimby Douglas'',
=MID(A1,8,6) -> Quimby
Returns e to the num power.
EXP(LN(num)) = num.
@EXP(4) -> 54.5982
@EXP(12%*5) * 30000 -> $54,664: This formula calculates the present value of $30,000 invested at 12% continually compounded interest for a term of 5 years.
Returns the integer portion of num.
@INT(@RAND*10) : This formula returns a random number between 0 and 9.
A3: 68293
@INT(@LOG(A3))+1 -> 5 : This formula tells how many digits are the number in cell A3.
Returns the smallest number in the range or list. String values are considered to be zero.
A1: 300
A2: 400
A3: Hello!
@MIN(A1:A3)-> 0 : This formula returns zero because the string "Hello!" evaluates to zero.
A1: 0.0002
A2: 0
A3: -339492
@MIN(A1:A3) -> -339492
Note: This function differs from MIN(num|range[,...]). @MIN() treats string value as zero, but MIN() ignores string values.
`Returns an error. All cells that refer to a cell with an error will evaluate to errors as well.
5Returns the ASCII character for num.
=CHAR(68) -> D
THISCOLUMN
@Returns the column number of the cell containing this constant.
@DATEVALUE
Converts a date string to a date serial number. If the value is a number, just returns the number.
@DATEVALUE("02/04/92") -> 8069
A3: 'Jul-94
@DATEVALUE(A3) -> 8947 : This is the date serial number for 1-Jul-94
Computes the present value of per payments of pmt with a rate of rate.
What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%?
@PV(30000,12%,20) -> $224,083.31
LOG10
Returns the logarithm, base 10, of num.
=LOG10(45) -> 1.6532
+10^(LOG(292)) -> 292 : This formula takes the base 10 log of 292, then raises 10 to that power, resulting in the number it began with.
Returns the natural logarithm of num.
@LN(27) -> 3.2958
B8: 1024
@LN(B8)/@LN(2) -> 10 : This formula returns the base 2 log of cell B8.
Computes the future value of per payments of pmt with the interest rate of rate.
If $2,000 were deposited every year into an account earning 9% annual interest, how much would the account be worth in 20 years?
@FV(2000,9%,20) = $102,320.24
Returns the Double Declining Balance depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 3?
year 1: =DDB(12000,2000,5,1) -> $4,800
year 2: =DDB(12000,2000,5,2) -> $2,880
year 3: =DDB(12000,2000,5,3) -> $1,728
@CTERM
Computes the number of periods required for an investment of pv to grow to fv at rate.
How long will it take for a $20,000 investment to grow to $40,000 at an annual rate of 5%?
@CTERM(0.05,20000,40000) -> 14.2 years
How many months will it take for a $4,000 investment to grow to $8,000, being compounded monthly at a rate of 8%?
YConverts all the characters in str to lower case.
@LOWER("Bill Johnson") = bill johnson
FORMAT
This function returns the given number num in the format specified by the format constant fmt-const. You may optionally specify the precision prec, the number of decimal places in the number.
=FORMAT(18.9, SCIENTIFICFORMAT) -> 1.89e+01
ISSTRING
Returns 1 is exp results in a string. Returns 0 otherwise.
=ISSTRING(VALUE("340")) -> 0
=ISSTRING(LEFT("Goodbye!",4)) -> 1
=ISSTRING("Some text") -> 1
Returns a special error, NA. All cells that refer to a cell with an error will evaluate to errors as well. @NA is useful to designate cells that have a value that is not available or defined.
@PERCENTFORMAT
bThis constant is used to specify the percent format.
@FORMAT(0.09389, @PERCENTFORMAT, 2) -> 9.39%
@EXACT
&Returns 1 if the strings match exactly, 0 otherwise.
@EXACT("Daytime","Daytyme") -> 0 (false)
A1: "hello"
B1: "hello"
@EXACT(A1,B1) -> 1 (true)
Note: When you use = to match strings, the comparison ignores case. For example, "Upper" = "upper" is true, but @EXACT("Upper", "upper") is false.
If exp is a string, returns exp; otherwise it returns a zero length string. This function is used to guarantee that an expression is a string.
@S(343) -> "" (blank string)
A1: 2000
A2: Orders
@S(A1)&@S(A2) = Orders
@DCOUNT
Counts all the numeric and string items matching criteria in the column offset from the upper left corner of the database range.
This is a sample database:
A B C D
1 NAME GRADE GPA GRADE
2 Joe B 3.2 B
3 Sue A 4.7
4 Robert C 2.7
5 Steve A 4.5
6 Ann B 4.2
The database range is A1:C6. The criteria range is D1:D2.
To count the number of students who earned B's use this formula:
@DCOUNT(A1:C6,2,D1:D2) -> 2
@VLOOKUP
Looks up code in the vertical table and returns the cell offset cells from the left side of the first match.
If the first column contains only strings and code is a number, it returns the value from the last row. If code is also a string, but doesn't match any of the table entries, !ERR is returned.
A B
1 A 5.0
2 B 4.0
3 C 3.0
4 D 2.0
5 F 0
@VLOOKUP("A",A1:B5,1) -> 5.0
@VLOOKUP("D",A1:B5,1) -> 2.0
@VLOOKUP("Incomplete",A1:B5,1) -> !ERR
@VLOOKUP(3.0,A1:B5,1) -> 0
COUNT
Counts the number of numeric entries. Cells or items containing blanks or strings are not counted.
A B
1 Duck 10
2 Soup 20
3 1.86 20
4 $300 10
5 '====== 30
6 50
7 40 70
=COUNT(A1:A7) -> 3 : There are only three items in column A because cells A1, A2, and A5 are strings and A6 is blank.
=COUNT(A1:A7,B1:B7) -> 10 : This counts the 3 items in column A and adds the 7 items in column B.
=COUNT("Good","Bad","Indifferent",0) -> 1
This function is different than @COUNT(num|range[,...]). COUNT() only counts numeric values. @COUNT() also counts string values.
greater than or equal to
ROUND
Rounds num to prec decimal places. If prec is less than zero, rounds to the left of the decimal place.
=ROUND(350.2852,2) -> 350.29
=ROUND(25492,-3) -> 25000
@ADDRESS
iReturns the address for the row and col specified. @ADDRESS(0,0) is cell A1.
B1: 15
@ADDRESS(0,1) -> 15
equal
less than or equal to
@HLOOKUP
%Looks up code in the horizontal table. Returns the cell offset cells from the top of the first match. If the first row contains numbers and code is less than the first value in the lookup row, !ERR is returned. If code is greater than the first value but no exact match is found, it returns the value from the last column.
If the first row contains only strings and no exact match is found, it returns !ERR.
A B C
1 1 2 3
2 $200 $40 $500
@HLOOKUP(2,A1:B3,1) -> $400
@HLOOKUP(0.5,A1:B3,1) -> !ERR
@HLOOKUP(5,A1:B3,1) -> $500
@FIXEDFORMAT
cThis constant is used to specify the fixed decimal format.
@FORMAT(42.692, @FIXEDFORMAT) -> 42.69
@REPLACE
Replaces num characters in str starting at character start with the string new-str.
@REPLACE("The cost is $450, COD",13,3,"2,500") -> The cost is $2,500, COD
@REPLACE("Robert J. Stevenson",7,1,"L") -> Robert L. Stevenson
NACONST
HOSTNAME
8Returns the name of the machine that Mesa is running on.
NEXTMONTH
Returns a date serial number one calendar month after date. If the day of the month is greater than 28, then it is set to 28.
A3: 27-Jul-92
=NEXTMONTH(A3) -> 8274 : This is the date-number for
27-Aug-92.
=NEXTMONTH(11381) -> 11409 : This is the date-number for 28-Mar-2001.
Returns a date serial number for a given time.
=TIME(11,30,00) -> 0.479167 : This is the date-number for 11:30 AM.
=NOW+TIME(8,0,0) : This formula returns a date-number for the time 8 hours from now.
@TERM
@TERM() computes the number of payments of pmt required to reach fv with a interest rate of rate.
How many $700 monthly payments would be needed to accumulate $250,000 in a bank account earning 4% interest?
@TERM(700,4%/12,250000) -> 235.6 monthly payments (taking almost 20 years).
@CALCRATE
Takes the formula in cell adr and executes it as if it had been copied and then pasted into the current cell. This is a powerful and valuable function. You can have a master function and a series of functions that are the same. Where you change the master function, the method of calculation used by the other functions is changed. You can use SAME() in conjunction with INDEX(), HLOOKUP(), VLOOKUP(), and to define a formula used in a calculation rather than having a complex IF() statement. An application for this is an employee pay calculation that depends on an employee type.
A1: 1
A2: 2
If B1: =A1 * 5 and B2: =SAME(B1), then B2 = 10 (A2*5).
If B1: =A1/5, then B2 changes to 0.4 (A2/5)
If B1: =SIN(A1), B2 changes to 0.9093 (SIN(A2))
CURRENTVALUE
SETBKGCOLOR
Sets the background color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
=SETBKGCOLOR(A1,IF(A1 > 50 && A1 < 100,1,0),0,0) : This sets the background color to red if the number is greater than 50 and less than 100, otherwise it sets the background color to black.
Sums all the items matching criteria in the column offset from the upper left corner of the database range.
This is a sample database:
A B C D
1 STATE REGION AVG RF REGION
2 AL South 3.7 South
3 CA West 2.1
4 NJ East 5.2
5 FL South 4.9
6 LA South 6.8
7 TX West 5.0
8 MA North 7.6
9 AZ West 2.2
10 NV West 2.4
The database range is A1:C10. The criteria range is D1:D2.
To find the total amount of rainfall for the Southern region use this formula:
=DSUM(A1:C10,2,D1:D2) -> 15.4
COMMAFORMAT
\This constant is used to specify the comma format.
=FORMAT(238.093, COMMAFORMAT) -> 238.09
MONTH
Returns the month for a date serial number.
=MONTH(DATEVALUE("12/24/92")) -> 12
=CHOOSE(MONTH(NOW)-1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") : This formula will display the current month.
Adds all the numbers or sums the numbers in the range. You can specify as many numbers or ranges as you want.
@SUM(300,400,2300,100) -> 3100
A1: Blue
A2: 300
A4: 900
B1: 1200
@SUM(A1:A4,B1) -> 2400
not equal to
<AND>
logical AND
Returns the day of the month for a date serial number.
A3: 18-Jul-92
@DAY(A3) -> 18
@DAY(@NOW) : This formula will return the current day of the month.
#AND#
NETWORKDAYS
Returns the number of working days between the date1 serial number and date2. Working days are all days other than Saturday, Sunday, and the optional holidays listed.
=NETWORKDAYS(NOW,NOW+10) : This returns the number of working days between now and 10 days from now.
tReturns the Absolute Value of num.
@ABS(-45.3) -> 45.3
@ABS(@COS(@PI))-> 1
@ABS(34) -> 34
@ABS("Absolutely!") -> 0
@BLUECOLOR
WA constant referring to BLUE for use in functions that change color (e.g. @SETCOLOR())
@CURRENTNUMBER
The current numeric value of a cell. Can be used for time series functions.
To remember the largest number entered into cell A1, use
@if(a1 > @currentnumber,a1,@currentnumber).
To count the number of times the worksheet was recalculated, use
@currentnumber + 1
DMYFORMAT
nThis constant is used to specify the date day-month-year format.
=FORMAT(9876.54321, DMYFORMAT) -> 15-Jan-97
STDDEV
@RANGE
Returns the address for the range specified by the upper row, left column, lower row, right column.. @RANGE(0,0,1,1) is the range a1:b2.
@ISREF
Returns 1 if exp is a cell or range reference. Returns 0 otherwise.
@ISREF(A1) -> 1 (true)
If RANGE1 has been assigned A1:C10, @ISREF(RANGE1) -> 1
@INDEX
Returns the cell that is row rows and col columns offset from the upper left corner of range.
A B C
1 10 20 30
2 3942 4932 5929
3 4920 5929 9294
@INDEX(A1:C3,2,2) = 9294
A10: 0
A11: 1
@INDEX(A1:C3,A10,A11) = 3942
Note: This function is different from INDEX(range,row,col). INDEX() specifies the offset in row then column order. @INDEX() specifies the offset in column then row order.
@UPPER
Converts all the characters in str to upper case.
@UPPER("Uppers only") -> UPPERS ONLY
@UPPER(@MID("robert c. smith",7,2)) -> C. : This formula uses @MID() to return the 8th and 9th characters from the string "Robert C. Smith". The @UPPER() function then capitalizes this result.
@STRING
DRounds num to prec decimal places and converts it to a string.
B3: 5251.769585987
@STRING(B3,6) -> 5251.769586
@STRING(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a string with 2 decimal places of precision and concatenates it with the text " is now a string".
logical OR
@COMMAFORMAT
]This constant is used to specify the comma format.
@FORMAT(238.093, @COMMAFORMAT) -> 238.09
@FORMAT
This function returns the given number num in the format specified by the format constant fmt-const. You may optionally specify the precision prec, the number of decimal places in the number.
@FORMAT(18.9, SCIENTIFICFORMAT) -> 1.89e+01
CYANCOLOR
VA constant referring to CYAN for use in functions that change color (e.g. SETCOLOR())
ADDRESS
hReturns the address for the row and col specified. ADDRESS(0,0) is cell A1.
B1: 15
=ADDRESS(0,1) -> 15
DAYMONFORMAT
bconstant is used to specify date day-month format.
=FORMAT(9876.54321, DAYMONTHFORMAT) -> 15-Jan
CURRENCYFORMAT
aThis constant is used to specify the currency format.
=FORMAT(555.9, CURRENCYFORMAT) -> $555.90
@MAGENTACOLOR
ZA constant referring to MAGENTA for use in functions that change color (e.g. @SETCOLOR())
@DMAX
Returns the maximum value of the records in database that match criteria. Strings have a value of zero.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C6. The criteria range is D1:D2.
To find the largest sales figure in the Eastern region use this formula:
@DMAX(A1:C6,1,D1:D2) -> 3000
MAGENTACOLOR
YA constant referring to MAGENTA for use in functions that change color (e.g. SETCOLOR())
Multiplies all the numbers in the parenthesis, or all the numbers in the range. You may specify a number of different ranges.
=PROD(3, 5, 10, 2) -> 300
A1: 3
A2: 5
B1: 10
=PROD(A1:A2, B1, 2) -> 300
@HOUR
Returns the hour for a date serial number.
@HOUR(@TIMEVALUE("23:00:00")) -> 23
@HOUR(@NOW) : This formula will return the current hour of day.
@DINTL1FORMAT
zThis constant is used to specify the numeric date month-day-year format.
@FORMAT(9876.54321, @DINTL1FORMAT) -> 01/15/97
@DSUM
Sums all the items matching criteria in the column offset from the upper left corner of the database range.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C10. The criteria range is D1:D2.
To find the total amount of rainfall for the Southern region use this formula:
@DSUM(A1:C10,2,D1:D2) -> 15.4
@ASIN
Returns the Arcsine of num (in radians). ASIN(SIN(num)) = num.
What is the angle whose sine = 0.3090?
@ASIN(0.3090) -> 0.3141 (radians): This is equivalent to an angle of 18 degrees.
@ASIN(0.3090)*180/@pi -> 18: This gives the same answer as above, but in degrees.
@DMIN
Returns the smallest numeric item in the records that match the criteria. String values will count as zeros.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C6. The criteria range is D1:D2.
To find the smallest sales figure in the Eastern region use this formula:
@DMIN(A1:C6,1,D1:D2) -> 2800
CLEAN
,Removes all control characters from string.
@PROD
Multiplies all the numbers in the parenthesis, or all the numbers in the range. You may specifie a number of different ranges.
@PROD(3, 5, 10, 2) -> 300
A1: 3
A2: 5
B1: 10
@PROD(A1:A2, B1, 2) -> 300
Note that cells containing strings are counted as zero.
@TINTL1FORMAT
|This constant is used to specify twenty four hour time format with seconds.
@FORMAT(9876.54321, @TINTL1FORMAT) -> 13:02:13
@SAME
Takes the formula in cell adr and executes it as if it had been copied and then pasted into the current cell. This is a powerful and valuable function. You can simply have a master formula and a series of formulas that are the same. Where you change the master formula, the method of calculation used by the other formulas is changed. You can use @SAME() in conjunction with @INDEX(), @HLOOKUP(), and @VLOOKUP() to define a formula used in a calculation rather than having a complex @IF() statement. An application for this is an employee pay calculation that depends on an employee type.
A1: 1
A2: 2
If B1: =A1 * 5 and B2: @SAME(B1), then B2 -> 10 (A2*5).
If B1: =A1/5, then B2 changes to 0.4 (A2/5)
If B1: @SIN(A1), B2 changes to 0.9093 (@sin(A2))
Returns the Sum of the Years Digits depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1, 2 and 4?
year 1: @SYD(12000,2000,5,1) -> $3,333
year 2: @SYD(12000,2000,5,2) -> $2,667
year 4: @SYD(12000,2000,5,4) -> $1,333
JReturns the straight-line depreciation of an asset that cost cst, has a salvage value of slvg, and a life of life.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off each year?
@SLN(12000,2000,5) -> $2,000
DPROD
Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are ignored.
nIf num is zero or not a number, 1 is the result; otherwise, 0 is the result.
@NOT(34) -> 0
@NOT("Text") -> 1
Computes the internal rate of return of an investment. String entries are considered zero values.
A rug-cleaning business is being offered for sale for $180,000. The owner says the business should net $25,000, $35,000, $39,000, $40,000 and $55,000 in years 1-5. What is this project's internal rate of return?
A
1 -180000
2 25000
3 35000
4 39000
5 40000
6 55000
The initial expense is entered as a negative number in cell A1. The range of cash inflows are given in cells A2:A6. To calculate this project's IRR value:
@IRR(B1,A1:A6) -> 2.3%
Note: This differs from IRR(guess,num|range[,...]). @IRR() counts string entries as zero, but IRR() ignores string entries.
Returns a string that is len characters taken from str starting start characters from the left of str.
@MID("Have a great day!",8,5) = great
A1: "Robert Quimby Douglas",
@MID(A1,8,6) = Quimby
MReturns the Cosine of num (in radians).
=COS(PI) -> -1
=COS(LN(3)) -> 0.4548
Returns the Double Declining Balance depreciation for an asset that cost cst, has a salvage value of slvg, a life of life in period per.
A company buys a delivery van for $12,000. It has a useful life of 5 years and will be worth $2,000 at the end of that time. How much depreciation can the company write off in years 1,2 and 3?
year 1: =DDB(12000,2000,5,1) -> $4,800
year 2: =DDB(12000,2000,5,2) -> $2,880
year 3: =DDB(12000,2000,5,3) -> $1,728
@ROUND
Rounds num to prec decimal places. If prec is less than zero, it rounds to the left of the decimal place.
@ROUND(350.2852,2) -> 350.29
@ROUND(25492,-3) -> 25000
SETCOLOR
Sets the color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
=SETCOLOR(A1,IF(A1 > 100,1,0),IF(A1 > 100,0,1),0) : This sets the color to red if the number is greater than 100, otherwise it sets the color to green.
@GENERALFORMAT
bThis constant is used to specify the general format.
@FORMAT(238.093, @GENERALFORMAT) -> 238.093
REDCOLOR
UA constant referring to RED for use in functions that change color (e.g. SETCOLOR())
WEEKDAY
Returns the day of the week for a date serial number, 0 - Sunday through 6 - Saturday.
=WEEKDAY(NOW-5) : This returns the day of the week 5 days prior to today.
A7: "6/20/82"
=WEEKDAY(DATEVALUE(A7)) -> 0 : Returns the day of the week for 20-Jun-82.
@PROPER
Converts str to "proper" capitalization. It will turn the first letter of each word to upper case; otherwise, it converts the letter to lower case.
@PROPER("MS. JANET KRAFT") = Ms. Janet Kraft
@PROPER("all entries posted before 7/2") = All Entries Posted Before 7/2
YELLOWCOLOR
XA constant referring to YELLOW for use in functions that change color (e.g. SETCOLOR())
@CONVDATE
Converts a number in the format YYMMDD to a date serial number. Dates after the year 2000 can be specified by placing a 1 before the year.
@CONVDATE(930210) -> 8441 : This is the date-number of
10-Feb-93
@CONVDATE(1100410) -> 14709 : This returns the date number of 10-Apr-2010.
THISROW
=Returns the row number of the cell containing this constant.
@HOSTNAME
@WORKDAY
Returns the date serial number days working days from the date serial number. Working days are all days other than Saturday, Sunday, and the optional holidays listed.
@WORKDAY(@NOW,10) : This returns the date serial number of the day 10 working days from now.
@NEXT
Returns the next logical value after value. For example, if value = "Mon", then @NEXT() returns "Tue". Values for months, days, and quarters are built in. If value is a number, @NEXT() adds 1 or inc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the next year. For example, @NEXT("Q4 `92") is "Q1 `93".
@NEXT("Q4 '92") = Q1 '93
@NEXT("January") = February
@NEXT("Region 1") = Region 2
GETINPUT
2Returns the user-typed input string for the cell.
DINTL2FORMAT
oThis constant is used to specify the numeric date month-day format.
=FORMAT(9876.54321, DINT2FORMAT) -> 01/15
Returns the interest rate for an investment of pv to achieve a value of fv in per periods.
How much interest would an a bank account need to earn for an investment of $67,000 to grow to $100,000 in 5 years or less?
RATE(100000,67000,5) = 8.34% or more.
Returns 1 if exp returns an NA error. Returns 0 otherwise.
=ISNA(@NA) -> 1 (true)
A1: 34
A2: 49
A3: =NA
=ISNA(SUM(A1:A3)) -> 1 : This formula returns 1 (true) because cell A3 contains NA, which causes SUM(A1:A3) to evaluate to NA also.
TIMEVALUE
WConverts a string to a date serial number.
A1: '10
A2: '58
A3: '29
=TIMEVALUE(A1&":"&A2&":"&A3) = 0.45728 : This formula takes the string contents of cells A1, A2 & A3 and concatenates them into the string "10:58:29", then converts that string to a time value.
A3: 12:34 am
=TIMEVALUE(A3) -> 0.023611 : This is the date-number for 12:34 am.
RAND()
@CELL
Returns information about a the cell at adr. atrb defines the type of information requested.
@CELL("address",U7) = $U$7
@CELL("row",U7) = 7
@CELL("col",U7) = 21
U7 = @PI, @CELL("contents",U7) = 3.141593
ADDRESS - returns the address of the cell
ROW - returns the row of the cell
COL - returns the column of the cell
CONTENTS - returns the contents of the cell
FORMAT - returns the format of the cell. Formats are:
G - General
Fn - Fixed Decimal n places
Sn - Scientific Format n decimal places
Cn - Currency format n decimal places
,n - Comma format n decimal places
+ - Chart Format
Pn - Percent Format n decimal places
T - Text Format
H - Hidden Format
D1 - Day Month Year Format
D2 - Day Month Format
D3 - Month Year Format
D6 - Hours Minutes Seconds Format
D7 - Hours Minutes Format
D4 - Int'l Date Format 1
D5 - Int'l Date Format 2
D8 - Int'l Time Format 1
D9 - Int'l Time Format 2
PREFIX - The string prefix in Lotus Format
PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise
TYPE - b - blank cell, v - number value, l - string value
WIDTH - Returns the width of the column in characters
RANGE - Converts a single address to a range
#NOT#
This is the logical NOT function, but it is not used the same way as the logical AND and OR. For example, #NOT# 1 will equal 0, while #NOT# 0 will equal 1.
A3 = +("Blue"="Red") , which returns 0 #NOT#A3 = 1 : The formula in cell A3 asks "is the string 'Blue' = the string 'Red'? The answer is 0 (false). The #NOT# function inverts this returning the answer 1 (true)
#NOT#1 = 0 (false)
@DSTD
Returns the standard deviation of the records that match the criteria. Strings have a value of zero.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C10. The criteria range is D1:D2.
To find the standard deviation of rainfall in the Western region use this formula:
@DSTD(A1:C10,2,D1:D2) -> 1.2029
RIGHT
Returns a string containing the num characters from the right side of str.
=RIGHT("The last ten characters",10) -> characters
=RIGHT("Ida M. Brown",5) -> Brown
Converts a date string to a date serial number. If the value is a number, just returns the number.
=DAYS("02/04/92") = 8069
A3: 'Jul-94
=DAYS(A3) = 8947 : This is the date number for
1-Jul-94
@CELLPOINTER
PReturns the value for the attribute of the currently active cell after a recalculation of the sheet. This is valuable for macros.
ADDRESS - returns the address of the cell
ROW - returns the row of the cell
COL - returns the column of the cell
CONTENTS - returns the contents of the cell
FORMAT - returns the format of the cell. Formats are:
G - General
Fn - Fixed Decimal n places
Sn - Scientific Format n decimal places
Cn - Currency format n decimal places
,n - Comma format n decimal places
+ - Chart Format
Pn - Percent Format n decimal places
T - Text Format
H - Hidden Format
D1 - Day Month Year Format
D2 - Day Month Format
D3 - Month Year Format
D6 - Hours Minutes Seconds Format
D7 - Hours Minutes Format
D4 - Int'l Date Format 1
D5 - Int'l Date Format 2
D8 - Int'l Time Format 1
D9 - Int'l Time Format 2
PREFIX - The string prefix in Lotus Format
PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise
TYPE - b - blank cell, v - number value, l - string value
WIDTH - Returns the width of the column in characters
RANGE - Converts a single address to a range
Returns the ASCII code for the first character in string.
CODE(``A'') = 65
CODE(``{'') = 123
CODE(``Hello'') = 72 : This formula is returning the ASCII code for ``H''
MReturns a date serial number for the year, month and day. A date serial number is the number of days since January 1, 1970. Dates after the year 2000 can be specified by placing a 1 before the year.
=DATE(92,8,24) -> 8271 : This is the date number for
24-Aug-92.
=DATE(79,10,31) -> 3590 : This is the date-number for
31-Oct-79.
TINTL2FORMAT
{This constant is used to specify twenty four hour time format without seconds.
=FORMAT(9876.54321, TINTL2FORMAT) -> 13:02
LTGRAYCOLOR
\A constant referring to Light GRAY for use in functions that change color (e.g. SETCOLOR())
@VALUE
@Returns the number value of string.
@VALUE("3.14159") -> 3.14159
@VALUE("1200") + 2000 -> 3200
@VALUE("320/104") -> !ERR
Note: This function differs from VALUE(string). If the argument to @VALUE() cannot be completely converted to a number, it returns an error, while VALUE() returns as much of the number as it can.
ATAN2
4Returns the Arctangent of x/y. This gives the angle of a line from the origin to x,y.
=ATAN2(1,3) -> 0.3218 (radians) : This is equivalent to an angle of 18.4 degrees.
Note: This function is different than @ATAN2(y,x). ATAN2() requires the X parameter to be first. @ATAN2() requires the Y parameter first.
IFELSE
Evaluates exp. If it is a non-zero value, the result is value1. If it is a zero or a string, value2 is the result.
=IFELSE(5>10,"Something is wrong!","Everything is normal.") -> Everything is normal.
WA constant referring to CYAN for use in functions that change color (e.g. @SETCOLOR())
@FALSE
Returns a logical false value.
@MINUTE
Returns the minute for a date serial number.
@MINUTE(@TIMEVALUE("11:35:20")) -> 35
@MINUTE(@NOW) : This formula will return the current number of minutes past the hour.
ISFORMULA
AReturns 1 if the cell at adr contains a formula, else returns 0.
@DPROD
Returns the product of all the items matching the criteria in the column offset from the upper left corner of the range. Strings are treated as zero values.
GREENCOLOR
WA constant referring to GREEN for use in functions that change color (e.g. SETCOLOR())
HOURMINFORMAT
kThis constant is used to specify date hours-minutes format.
=FORMAT(9876.54321, HOURMINFORMAT) -> 1:02 PM
@REDCOLOR
VA constant referring to RED for use in functions that change color (e.g. @SETCOLOR())
ISBLANK
@SCIENTIFICFORMAT
oThis constant is used to specify the scientific notation format.
@FORMAT(1.69, @SCIENTIFICFORMAT) -> 1.89e+01
@PATHNAME
DAVERAGE
CHARTFORMAT
UThis constant is used to specify the chart format.
=FORMAT(7, CHARTFORMAT) -> +++++++
SCIENTIFICFORMAT
nThis constant is used to specify the scientific notation format.
=FORMAT(1.69, SCIENTIFICFORMAT) -> 1.89e+01
Returns the year for a date serial number.
=YEAR(NOW-1000) : This returns the year of the date 1,000 days prior to the current date.
A7: '6/20/82
=YEAR(DATEVALUE(A7)) -> 82 : This formula extract the year from the date-string "6/20/82".
SETALTCOLOR
Sets the alternate color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
=SETALTCOLOR(A1,IF(A1 < -100,1,0),0,0) : This sets the alternate color to red if the number is less than -100, otherwise it sets the alternate color to black.
exclusive OR
Returns string repeated times times.
=REPEAT("X",10) -> XXXXXXXXXX
=REPT("over and ",4) -> over and over and over and over and
Returns 0 if num is zero, -1 if num is less than 0, and 1 if num is greater than 0.
B7: +RAND
=SIGN(B7-0.5) : This formula returns the sign of a randomly generated number between 0.5 and -0.5.
=SGN(-405) -> -1
VALUE
Returns the number value of string. If string begins with a number but contains non-numbers, VALUE() returns as much as it can. If string begins with a letter, it returns 0.
=VALUE("3.14159") = 3.14159
=VALUE("1200") + 2000 = 3200
=VALUE("320/104") = 320
Note: This function is different than @VALUE(string). If the argument to VALUE() cannot be completely converted to a number, it returns as much of the number as possible. @VALUE() returns an error.
Returns the next logical value after value. For example, if value = "Mon", then NEXT() returns "Tue". Values for months, days, and quarters are built in. If value is a number, NEXT() adds 1 or inc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the next year. For example, NEXT("Q4 '92") is "Q1 '93".
=NEXT("Q4 '92") -> Q1 '93
=NEXT("January") -> February
=NEXT("Region 1") -> Region 2
@NETWORKDAYS
Returns the number of working days between the date1 serial number and date2. Working days are all days other than Saturday, Sunday, and the optional holidays listed.
@NETWORKDAYS(@NOW,@NOW+10) : This returns the number of working days between now and 10 days from now.
Returns the hour for a date serial number.
=HOUR(TIMEVALUE("23:00:00")) -> 23
=HOUR(NOW) : This formula will return the current hour of day.
@ACOS
Returns the Arccosine of num (in radians). ACOS(COS(num)) = num.
What is the angle with the cosine = 0.7071?
@ACOS(0.7071) -> 0.7854 (radians): This is equivalent to an angle of 45 degrees.
@ACOS(0.7071)*180/@pi -> 45: This gives the same answer as above, but in degrees.
@TRUE
WHITECOLOR
WA constant referring to WHITE for use in functions that change color (e.g. SETCOLOR())
FILENAME
Returns the current file name.
@CHARTFORMAT
VThis constant is used to specify the chart format.
@FORMAT(7, @CHARTFORMAT) -> +++++++
[Computes the Sine of the number. The number is expressed in radians.
=SIN(60*PI/180) = 0.8660 : This formula gives the sine of 60 degrees.
A mountain road goes up at an incline of 25 degrees. If the road is straight and is 20 miles long, what vertical distance will a car travel to climb it? The formula is +20 * SIN(25*(pi/180)) -> 8.45 miles
The result is the statistical variance of the numbers. Strings and blank cells are not counted.
A B
1 NAME SCORE
2 Anna 65.8
3 Bill 95.4
4 Donna 30.2
5 Mark 54.9
6 Marie 35.1
7 Susan 75.9
8 John 83.2
9 Rob 33.1
10 Ethan 81.8
=VAR(B2:B10) -> 531.6010
Note: This function is different than @VAR(num|range[,...]). VAR() ignores string values. @VAR() treats string values as zero.
@BLACKCOLOR
XA constant referring to BLACK for use in functions that change color (e.g. @SETCOLOR())
?Returns the Tangent of num (in radians).
=TAN(1.24) -> 2.9119
CURRENTNUMBER
The current numeric value of a cell. Can be used for time series functions.
To remember the largest number entered into cell A1, use
=if(a1 > currentnumber,a1,currentnumber).
To count the number of times the worksheet was recalculated, use
=currentnumber + 1
Results in the standard deviation of the ranges or list. Strings are counted as 0, and blank cells are not counted.
A B
1 NAME SCORE
2 Anna 65.8
3 Bill 95.4
4 Donna 30.2
5 Mark 54.9
6 Maria 35.1
7 Susan 75.9
8 John 83.2
9 Rob 33.1
10 Ethan 81.8
@STD(B2:B10) -> 23.1
Note: This function differs from STDDEV(num|range[,...]) or STDEV(num|range[,...]). @STD() counts strings as zero, but STDDEV() and STDEV() ignore string values.
@FILENAME
Returns e to the num power.
EXP(LN(num)) = num.
=EXP(4) -> 54.5982
=EXP(12%*5) * 30000 = $54,664 : This formula calculates the present value of $30,000 invested at 12% continually compounded interest for a term of 5 years.
Returns the integer portion of the number num.
=INT(RAND*10) : This formula returns a random number between 0 and 9.
A3: 68293
+INT(LOG(A3))+1 -> 5 : This formula tells how many digits are the number in cell A3.
FIXED
BRounds num to prec decimal places and converts it to a string.
B3: 5251.769585987
=FIXED(B3,6) = 5251.769586
=FIXED(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a string with 2 decimal places of precision and concatenates it with the text " is now a string".
gResults in the smallest number in the range or list.
A1: 300, A2: 400, A3: Hello!
=MIN(A1:A3) -> 300 : This formula returns 300, and the string entry in cell A3 is ignored.
A1: 0.0002, A2: 0, A3: -339492
+MIN(A1:A3) -> -339492
Note: This function is different than @MIN(num|range[,...]). MIN() ignores string values. @MIN() treats string values as zero.
Returns the Arctangent of num (in radians). ATAN(TAN(num)) = num
=ATAN(5.6713) -> 1.3963 (radians) : This is equivalent to an angle of 80 degrees.
?Returns the Tangent of num (in radians).
@TAN(1.24) -> 2.9119
Returns the number of characters in the string.
LENGTH("10% discount on COD") = 19
A1: "A short string "
B1: "sentence"
=LEN(A1) -> 15
=LENGTH(A1&B1) -> 23
Returns information about a the cell at adr. Atrb defines the type of information requested.
U7: PI
=CELL("address",U7) -> $U$7
=CELL("row",U7) -> 7
=CELL("col",U7) -> 21
=CELL("contents",U7) -> 3.141593
ADDRESS - returns the address of the cell
ROW - returns the row of the cell
COL - returns the column of the cell
CONTENTS - returns the contents of the cell
FORMAT - returns the format of the cell. Formats are:
G - General
Fn - Fixed Decimal n places
Sn - Scientific Format n decimal places
Cn - Currency format n decimal places
,n - Comma format n decimal places
+ - Chart Format
Pn - Percent Format n decimal places
T - Text Format
H - Hidden Format
D1 - Day Month Year Format
D2 - Day Month Format
D3 - Month Year Format
D6 - Hours Minutes Seconds Format
D7 - Hours Minutes Format
D4 - International Date Format 1
D5 - International Date Format 2
D8 - International Time Format 1
D9 - International Time Format 2
PREFIX - The string prefix in Lotus Format
PROTECT - Returns 0 if the cell is Unprotected, 1 otherwise
TYPE - b - blank cell, v - number value, l - string value
WIDTH - Returns the width of the column in characters
RANGE - Converts a single address to a range
Returns the number of characters in string.
@LENGTH("10% discount on COD") -> 19
A1: "A short string "
B1: "sentence"
@LEN(A1) = 14
@LENGTH(A1&B1) = 22
zReturns the integer portion of the division of num divided by div. This complements the @MOD() function.
@DIV(12,5) -> 2
yReturns the integer portion of the division of num divided by div. This complements the MOD() function.
=DIV(12,5) -> 2
@GREENCOLOR
XA constant referring to GREEN for use in functions that change color (e.g. @SETCOLOR())
MINUTE
Returns the minute for a date serial number.
=MINUTE(TIMEVALUE("11:35:20")) -> 35
=MINUTE(NOW) : This formula will return the current number of minutes past the hour.
@HMSFORMAT
tThis constant is used to specify date hours-minutes-seconds format.
@FORMAT(9876.54321, @HMSFORMAT) -> 1:02:13 PM
OComputes the present value of an investment with a given interest rate, number of periods, and periodic payment. If a future value is not given, it is assumed to be 0. If type is not zero, payments are assumed to start in the next period.
What is the present value of the retirement annuity which promises to pay $30,000 every year for the next 20 years, given an interest rate of 12%?
=PV(12%,20,-30000) -> $224,083.31
What is the present value of the investment which promises to pay $225,000 at the end of 20 years, given an interest rate of 12%?
=PV(12%,20,,-225000) -> $23,325.02
VLOOKUP
Looks up code in the vertical table. Returns the cell offset cells from the left side of the first match.
If the first column contains only strings and code is a number, it returns the value from the last row. If code is also a string, but doesn't match any of the table entries, !ERR is returned.
A B
1 A 5.0
2 B 4.0
3 C 3.0
4 D 2.0
5 F 0
=VLOOKUP("A",A1:B5,1) -> 5.0
=VLOOKUP("D",A1:B5,1) -> 2.0
=VLOOKUP("Incomplete",A1:B5,1) -> !ERR
=VLOOKUP(3.0,A1:B5,1) -> 0
REPEAT
Returns string repeated times times.
=REPEAT("X",10) -> XXXXXXXXXX
=REPT("over and ",4) -> over and over and over and over and
SECOND
Returns the seconds of a date serial number.
=SECOND(TIMEVALUE("10:31:19")) -> 19
=SECOND(0.26903) -> 24 : This formula returns the seconds for the date-value for 06:27:24 AM.
SEvaluates exp. If it is a true or non-zero value, the result is value1. If it is false, zero, or a string, optional value2 is the result; otherwise, a blank is the result.
@IF(5>10,"Something is wrong!","Everything is normal.")
-> Everything is normal.
A1: 3400
A2: 4000
A3: 5000
@IF(@SUM(A1:A3)>=12400,"Full Capacity") = Full Capacity
CHOOSE
Chooses the num item from the list.
=CHOOSE(2,"first","second","third","fourth") -> third
=CHOOSE(MOD(INT(NOW),7),"Thu","Fri","Sat",Sun","Mon","Tue","Wed") : This function returns the current day using NOW to provide the current date.
Returns the natural logarithm (log base e) of num.
=LN(27) -> 3.2958
B8: 1024
=LN(B8)/LN(2) -> 10 : This formula returns the base 2 log of cell B8.
ISTEXT
Returns 1 is exp results in a string. Returns 0 otherwise.
=ISTEXT(VALUE("340")) -> 0
=ISTEXT(LEFT("Goodbye!",4)) -> 1
=ISTEXT("Some text") -> 1
Returns the Future Value of a stream of payments over pers periods at rate interest rate. If present value pv is not specified, it is assumed to be 0. Type designates when the payments start.
If $2,000 were deposited every year into an account earning 9% annual interest, how much would the account be worth in 20 years?
=FV(9%,20,-2000) -> $102,320.24
If a $50,000 were deposited instead and accrued 9% interest from the start of the year, what would the account be worth in 20 years?
=FV(9%,20,,-50000,1) -> $ 280,220.54
ISEMPTY
REvaluates exp. If it is a true or non-zero value, the result is value1. If it is false, zero, or a string, optional value2 is the result; otherwise, a blank is the result.
=IF(5>10,"Something is wrong!","Everything is normal.")
-> Everything is normal.
A1: 3400
A2: 4000
A3: 5000
=IF(SUM(A1:A3)>=12400,"Full Capacity") = Full Capacity
@USERNAME
If exp is a number, @N() returns exp, otherwise @N() returns 0. @N() is guaranteed to return a number and is used in functions that require numbers as parameters.
@N(@STRING(300,0)) -> 0
@N(34) -> 34
@RIGHT
Returns a string containing the num characters from the right side of str.
@RIGHT("The last ten characters",10) -> characters
@RIGHT("Ida M. Brown",5) -> Brown
@SECOND
Returns the seconds of a date serial number.
@SECOND(@TIMEVALUE("10:31:19")) -> 19
@SECOND(0.26903) -> 24 : This formula returns the seconds for the date-value for 06:27:24 AM.
WORKDAY
Returns the date serial number days working days from the date serial number. Working days are all days other than Saturday, Sunday, and the optional holidays listed.
=WORKDAY(NOW,10) : This returns the date serial number 10 working days from now.
@CHOOSE
Chooses the item offset from the first item on the list.
@CHOOSE(2,"first","second","third","fourth") -> third
@CHOOSE(@MOD(@INT(@NOW),7),"Thu","Fri","Sat",Sun","Mon","Tue","Wed") : This function returns the current day using @NOW to provide the current date.
@CLEAN
RADTODEG
fConverts num (in radians) to degrees. This complements the DEGTORAD() function.
=RADTODEG(PI) -> 180
HLOOKUP
)Looks up code in the horizontal table. Returns the cell offset cells from the top of the first match.
If the first row contains numbers and code is less than the first value in the lookup row, !ERR is returned. If code is greater than the first value but no exact match is found, it returns the value from the last column.
If the first row contains only strings and no exact match is found, it returns !ERR.
A B C
1 1 2 3
2 $200 $40 $500
=HLOOKUP(2,A1:C2,1) -> $400
=HLOOKUP(0.5,A1:C2,1) -> !ERR
=HLOOKUP(5,A1:C2,1) -> $500
@SIGNAL
FIf the condition is true, sends range of cells to the named Mach port.
MONYEARFORMAT
gThis constant is used to specify date month-year format.
=FORMAT(9876.54321, MONYEARFORMAT) -> Jan-97
Evaluates string as a cell address and returns that address.
A3: Cheers
B3: A3
@@(B3) -> Cheers
A1: 10
A2: 20
A3: 30
B1: A1:A3
@SUM(@@(B1)) -> 60
ISERR
Returns 1 if exp returns an error other than @NA. Returns 0 otherwise.
=ISERR(ERRORCONST) -> 1 (true)
=ISERR(@VALUE("ABCD") -> 1 (true) : This formula tried to convert "ABCD" to a value using @VALUE(). This returns !ERR, which causes ISERR() to returns 1.
DATEVALUE
Converts a date string to a date serial number. If the value is a number, just returns the number.
=DATEVALUE("02/04/92") = 8069
A3: 'Jul-94
=DATEVALUE(A3) = 8947 : This is the date number for
1-Jul-94
exponentiation
equal to or greater than
@SQRT
Returns the square root on the number.
@SQRT(34) -> 5.8310
@SQRT(@LOG(200)) -> 1.5169
@SQRT(@VAR(200,500,100)) -> 170 : This formula computes the standard deviation of the values given in the variance function.
BLUECOLOR
VA constant referring to BLUE for use in functions that change color (e.g. SETCOLOR())
@DEGTORAD
lConverts num (in degrees) to radians. This complements the @RADTODEG() function.
@DEGTORAD(180) -> 3.14159
equal to or less than
Removes leading and trailing spaces from string. It also converts multiple consecutive spaces in string to single spaces.
A3: Account # 3040302 40303
=TRIM(A3) -> Account # 3040302 40303
=TRIM(" Anything at all.") = Anything at all.
link worksheets
GENERALFORMAT
aThis constant is used to specify the general format.
=FORMAT(238.093, GENERALFORMAT) -> 238.093
@LEFT
Returns a string containing the first num characters from the left of str.
@LEFT("The first twelve characters",12) -> The first tw
@LEFT("ABCDEFG",4) -> ABCD
If exp is a string, returns exp; otherwise it returns a zero length string. This function is used to guarantee that an expression is a string.
T(343) -> "" (blank string)
A1: 2000
A2: Orders
T(A1)&T(A2) = Orders
@SETBKGCOLOR
%Sets the background color of the current cell and returns value. Red, Green, and Blue are numbers between 0 and 1 that set the percentage of red, green, and blue that will be mixed together to create the color. White is all ones and black is all zeros. If any of the color components are out of bounds or result in errors, the color is not changed.
@SETBKGCOLOR(A1,@IF(A1 > 50 #AND# A1 < 100,1,0),0,0) : This formula sets the background color to red if the number is greater than 50 and less than 100, otherwise sets the background color to black.
@YELLOWCOLOR
YA constant referring to YELLOW for use in functions that change color (e.g. @SETCOLOR())
ISNUMBER
-Returns 1 if exp returns a numeric result. Returns 0 otherwise.
=ISNUMBER(FIXED(300,0)) -> 0 : This formula converts the number "300" to a string using FIXED(). It then uses ISNUMBER() to test if that result is a value and returns 0 (false).
=ISNUMBER(34) -> 1
A3: +394 - 100
=ISNUMBER(A3) -> 1
DIDCHANGE
If exp is a number, N() returns exp, otherwise N() returns 0. N() is guaranteed to return a number and is used in functions that require numbers as parameters.
N(STRING(300,0)) = 0
N(34) = 34
<XOR>
Returns the number of columns in a range.
=COLS(A1:H20) -> 8
If RANGE1 is the name assigned to the range B1:CC200, =COLS(RANGE1) -> 80
@RAND
EReturns a random number between 0 and 1,including 0 and excluding 1.
@ISNA
Returns 1 if exp returns an @NA error. Returns 0 otherwise.
@ISNA(@NA) -> 1 (true)
A1: 34
A2: 49
A3: @NA
@ISNA(@SUM(A1:A3)) -> 1 : This formula returns 1 (true) because cell A3 contains @NA, which causes @SUM(A1:A3) to evaluate to @NA also.
@DAVG
Takes the average of numeric records that match criteria. Strings have a value of zero.
This is a sample database:
A B C D
1 NAME GRADE GPA GRADE
2 Joe B 3.2 B
3 Sue A 4.7
4 Robert C 2.7
5 Steve A 4.5
6 Ann B 4.2
The database range is A1:C6. The criteria range is D1:D2.
To calculate the average GPA of everyone who earned a B use this formula:
@DAVG(A1:C6,2,D1:D2) -> 3.7
@FIND
^Returns the location of sub-str in str. It starts searching for sub-str offset characters from the left of str. Returns !ERR if the string is not found.
@FIND("hampton","Northampton",1) -> 5
@FIND("hampton","Northampton",6) -> !ERR (because it starts searching at the 5th character)
A1: "The third word in this sentence."
@FIND("word",A1,1) -> 10
@DAVE
RANDOM
greater than
equal to
less than
@ISFORMULA
BLACKCOLOR
WA constant referring to BLACK for use in functions that change color (e.g. SETCOLOR())
ISERROR
Returns 1 if exp returns an error other than @NA. Returns 0 otherwise.
ISERROR(VALUE(``ABCD'') = 1 (true) : This formula tried to convert ``ABCD'' to a value using VALUE(). This returns !ERR, which causes ISERROR() to returns 1.
"Jun","Jul","Aug","Sep","Oct","Nov","Dec") : This formula will display the current month.
@MONTH(@DATEVALUE("12/24/92")) -> 12
SIGNAL
HIf the condition is true, sends range of cells to the named Mach port.
DOLLAR
:Converts a number to a dollar format string.
B3: 5251.769585987
=DOLLAR(B3,6) = $5251.769586
=DOLLAR(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a dollar string with 2 decimal places of precision and concatenates it with the text " is now a string".
division
ERRORCONST
subtraction
addition
HMSFORMAT
rThis constant is used to specify date hours-minutes-seconds format.
=FORMAT(9876.54321, HMSFORMAT) -> 1:02:13 PM
multiplication
@GETINPUT
string concatenation
LOWER
ZConverts all the characters in str to lower case.
LOWER(``Bill Johnson'') = bill johnson
@RADTODEG
hConverts num (in radians) to degrees. This complements the @DEGTORAD() function.
@RADTODEG(@PI) -> 180
DSTDEV
jReturns the standard deviation of the numeric records that match the criteria.
This is a sample database:
A B C D
1 STATE REGION AVG RF REGION
2 AL South 3.7 West
3 CA West 2.1
4 NJ East 5.2
5 FL South 4.9
6 LA South 6.8
7 TX West 5.0
8 MA North 7.6
9 AZ West 2.2
10 NV West 2.4
The database range is A1:C10. The criteria range is D1:D2.
To find the standard deviation of rainfall in the Western region use this formula:
=DSTDEV(A1:C10,2,D1:D2) -> 1.2029
@LENGTH
NEXTYEAR
Returns the next year of the date. If the date is 2/29 of a leap year, the date returned is 2/28 of the following year.
=NEXTYEAR(NOW) : This formula returns a date-number one year from the current date.
=NEXTYEAR(DATE(93,6,20)) -> 8936 : This is the date-number for 20-Jun-94.
DEGTORAD
kConverts num (in degrees) to radians. This complements the RADTODEG() function.
=DEGTORAD(180) -> 3.14159
@WEEKDAY
Returns the day of the week for a date serial number, 0 - Sunday through 6 - Saturday.
@WEEKDAY(@NOW-5) : This returns the day of the week 5 days prior to today.
A7: "6/20/82"
@WEEKDAY(@DATEVALUE(A7)) -> 0 : Returns the day of the week for 20-Jun-82.
@LTGRAYCOLOR
]A constant referring to Light GRAY for use in functions that change color (e.g. @SETCOLOR())
@YEAR
Returns the year for a date serial number.
@YEAR(@NOW-1000) : This returns the year of the date 1,000 days prior to the current date.
A7: "6/20/82"
@YEAR(@DATEVALUE(A7)) -> 82 : This formula extract the year from the date-string "6/20/82".
/Returns the nth root of num.
=ROOT(81,4) -> 3
DSTDDEV
HISTOGRAM
*Groups the values in range into #-of-divisions divisions, counts the number of items in each division. The formula should appear in #-of-divisions cells, starting at anchor. It returns the number of items in the division that is offset from the anchor point. Min and max are optional parameters that set the minimum and maximun values of the histogram range, so that the distribution will only be calculated for values between min and max.
A B
1 Name GPA
2 Joe 2.6
3 Sue 3.7
4 Robert 3.2
5 Steve 3.0
6 Ann 3.8
7 John 3.5
8 Mary 2.9
9 Chris 3.5
To divide the students into 5 divisions by GPA and determine how many fall into each division:
A10: =HISTOGRAM($B$2:$B$9,5,$A$10) -> 1
B10: =SAME($A$10) -> 2
C10: =SAME($A$10) -> 1
D10: =SAME($A$10) -> 2
E10: =SAME($A$10) -> 2
RANGE
Returns the address for the range specified by the upper row, left column, lower row, right column.. RANGE(0,0,1,1) is the range a1:b2.
Adds all the numbers or sums the numbers in the range. You can specify as many numbers or ranges as you want.
=SUM(300,400,2300,100) -> 3100
A1: Blue
A2: 300
A4: 900
B1: 1200
=SUM(A1:A4,B1) -> 2400
@TIME
Returns a date serial number for a given time.
@TIME(11,30,00) -> 0.479167 : This is the date-number for 11:30 AM.
@NOW+@TIME(8,0,0) : This formula returns a date-number for the time 8 hours from now.
<Returns the Net Present Value of an investment with the interest of rate. Counts string values in range as zero.
An apartment building is being offered for sale for $400,000. Expected rental incomes for years 1-5 are $79,000, $85,000, $85,000, $103,000 and $120,000 and the interest rate is expected to stay close to 7%. Is this a worthwhile investment?
A
1 -400000
2 79000
3 85000
4 85000
5 103000
6 120000
To figure out the net present value of the cash in-flows:
@NPV(7%,A2:A6) -> $381,595
To figure out the value of the project:
Project Value = Initial Cost + NPV
+A1+@NPV(7%,A2:A6) -> -18,404
No, this project is not worthwhile.
Note: This differs from NPV(rate,pmt1[,pmt2[,...]]). @NPV() treats string values as zero, but NPV() ignores string values.
Returns the payment to pay off a loan of prin with interest int over pers payments.
What would be the monthly payment on an $40,000 home improvement loan, to be repaid in 5 years at a fixed rate of 9%?
@PMT(40000,9%/12,5*12) -> $830.33 per month.
Returns the maximum numeric value of the records in database that match criteria.
This is a sample database:
A B C D
1 NAME SALES REGION REGION
2 Joe 3000 East East
3 Sue 4500 North
4 Robert 2800 East
5 Steve 3700 South
6 Ann 2400 West
The database range is A1:C6. The criteria range is D1:D2.
To find the largest sales figure in the Eastern region use this formula:
=DMAX(A1:C6,1,D1:D2) = 3000
Returns the remainder of num divided by div
@MOD(12,5) -> 2
@NOW-@MOD(@NOW,7)+5 : This function returns the date-number for Monday of the current week.
Returns the largest number in the range or list. Strings are considered to be zero.
@MAX(4.5,3.2,2.5,2.5,6.2) = 6.2
@MAX(4,4,4) = 4
A B C
1 300 400
2 0 400
3 200 100
4 300 5000
5 700
6 600
7 300
8 500
@MAX(A1:A8,C1:C8) -> 5000
Note: This function differs from MAX(num|range[,...]). @MAX() treats string values as zero, but MAX() ignores string values.
FALSE
@DMYFORMAT
oThis constant is used to specify the date day-month-year format.
(.txt)